/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package personal.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import personal.obj.Bill;
import personal.utility.DBUtility;

/**
 *
 * @author PHATNBSE61057
 */
public class BillDAO implements Serializable {

    private final DBUtility utility = new DBUtility();

    public BillDAO() {
    }

    public Bill getBillByID(int billID) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Bill result = new Bill();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Bill WHERE Order_Bill_ID = ?";
            pst = con.prepareStatement(query);
            pst.setInt(1, billID);
            rs = pst.executeQuery();
            if (rs.next()) {
                int orderBillID = rs.getInt("Order_Bill_ID");
                String title = rs.getString("Title");
                String username = rs.getString("Username");
                String fullname = rs.getString("Fullname");
                String address = rs.getString("Address");
                String town = rs.getString("Town");
                String county = rs.getString("County");
                String country = rs.getString("Country");
                String postcode = rs.getString("Postcode");
                String email = rs.getString("Email");
                String phone = rs.getString("Phone");
                String recipientTitle = rs.getString("RecipientTitle");
                String recipientFullname = rs.getString("RecipientFullname");
                String recipientAddress = rs.getString("RecipientAddress");
                String recipientTown = rs.getString("RecipientTown");
                String recipientCounty = rs.getString("RecipientCounty");
                String recipientCountry = rs.getString("RecipientCountry");
                String recipientPostcode = rs.getString("RecipientPostcode");
                String recipientEmail = rs.getString("RecipientEmail");
                String recipientPhone = rs.getString("RecipientPhone");
                double subtotal = rs.getDouble("Subtotal");
                double total = rs.getDouble("Total");
                int shippingID = rs.getInt("ShippingID");
                int paymentID = rs.getInt("PaymentID");
                result = new Bill(orderBillID, title, username, fullname,
                        address, town, county, postcode, email, phone,
                        country, recipientTitle, recipientFullname,
                        recipientAddress, recipientTown, recipientCounty,
                        recipientPostcode, recipientEmail, recipientPhone,
                        recipientCountry, subtotal, total, shippingID,
                        paymentID);
                return result;
            }

        } catch (Exception e) {
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
            }
        }
        return null;
    }

    public boolean insertBill(Bill bill) {
        Connection con = null;
        PreparedStatement pst = null;
        int rowChanged;
        try {
            con = utility.makeConnection();
            String query = "INSERT INTO Bill (Order_Bill_ID, Title, Username, "
                    + "Fullname, [Address], Town, County, Country, Postcode, "
                    + "Email, Phone, RecipientTitle, RecipientFullname, "
                    + "RecipientAddress, RecipientTown, RecipientCounty, "
                    + "RecipientCountry, RecipientPostcode, RecipientEmail, "
                    + "RecipientPhone, Subtotal, Total, ShippingID, PaymentID) "
                    + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            pst = con.prepareStatement(query);
            pst.setInt(1, bill.getOrderBillID());
            pst.setString(2, bill.getTitle());
            pst.setString(3, bill.getUsername());
            pst.setString(4, bill.getFullname());
            pst.setString(5, bill.getAddress());
            pst.setString(6, bill.getTown());
            pst.setString(7, bill.getCounty());
            pst.setString(8, bill.getCountry());
            pst.setString(9, bill.getPostcode());
            pst.setString(10, bill.getEmail());
            pst.setString(11, bill.getPhone());
            pst.setString(12, bill.getRecipientTitle());
            pst.setString(13, bill.getRecipientFullname());
            pst.setString(14, bill.getRecipientAddress());
            pst.setString(15, bill.getRecipientTown());
            pst.setString(16, bill.getRecipientCounty());
            pst.setString(17, bill.getRecipientCountry());
            pst.setString(18, bill.getRecipientPostcode());
            pst.setString(19, bill.getRecipientEmail());
            pst.setString(20, bill.getRecipientPhone());
            pst.setDouble(21, bill.getSubtotal());
            pst.setDouble(22, bill.getTotal());
            pst.setInt(23, bill.getShippingID());
            pst.setInt(24, bill.getPaymentID());
            rowChanged = pst.executeUpdate();
            if (rowChanged > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public Bill getBillByOrderID() {
        return null;
    }
}
